﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Configuration;

#pragma warning disable 618

namespace Rookey.Frame.IMCore
{
    class SqlServerAccountStorage : IAccountStorage
    {
        string m_ConnectionString = "";

        public SqlServerAccountStorage()
        {
            string path = HttpContext.Current == null || HttpContext.Current.Request.ApplicationPath == "/" ? "/IM" : HttpContext.Current.Request.ApplicationPath + "/IM";
            Configuration config = WebConfigurationManager.OpenWebConfiguration(path);

            m_ConnectionString = config.ConnectionStrings.ConnectionStrings["IM_ConnectString"].ConnectionString;
        }

        DataRow IAccountStorage.GetAccountInfo(string name)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetAccountInfoByName";

            cmd.Parameters.Add("name", DbType.String).Value = name;

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd;

            DataTable dt = new DataTable();
            try
            {
                ada.Fill(dt);
            }
            catch { }
            finally
            {
                ada.Dispose();
                conn.Dispose();
            }
            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }

        DataRow IAccountStorage.GetAccountInfo(Int64 key)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "GetAccountInfoByID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("id", DbType.Int64).Value = key;

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd;

            DataTable dt = new DataTable();
            ada.Fill(dt);
            ada.Dispose();

            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }

        DataRowCollection IAccountStorage.GetAllUsers()
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"GetAllUsers";
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd;

            DataTable dt = new DataTable();
            ada.Fill(dt);
            ada.Dispose();

            return dt.Rows;
        }

        DataRowCollection IAccountStorage.GetAllGroups()
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"GetAllGroups";
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd;

            DataTable dt = new DataTable();
            ada.Fill(dt);
            ada.Dispose();

            return dt.Rows;
        }

        DataRowCollection IAccountStorage.GetFriends(string name)
        {
            DataTable result = new DataTable();

            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "GetFriends";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("name", DbType.String).Value = name;

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd;

            ada.Fill(result);
            ada.Dispose();

            return result.Rows;
        }

        Int64 IAccountStorage.GetRelationship(string account1, string account2)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"GetRelationship";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("account1", DbType.String).Value = account1;
            cmd.Parameters.Add("account2", DbType.String).Value = account2;

            DataTable result = new DataTable();

            SqlDataAdapter ada = new SqlDataAdapter();

            ada.SelectCommand = cmd;
            ada.Fill(result);
            ada.Dispose();

            return result.Rows.Count > 0 ? Convert.ToInt64(result.Rows[0]["Relationship"]) : -1;
        }

        bool IAccountStorage.Validate(string name, string password)
        {
            try
            {
                SqlConnection conn = new SqlConnection(m_ConnectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Validate";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("name", DbType.String).Value = name;
                cmd.Parameters.Add("password", DbType.String).Value = Utility.MD5(password);

                SqlDataAdapter ada = new SqlDataAdapter();
                ada.SelectCommand = cmd;

                DataTable dt = new DataTable();
                ada.Fill(dt);
                ada.Dispose();

                return dt.Rows.Count > 0;
            }
            catch { }
            return false;
        }

        String[] IAccountStorage.GetUserRoles(string name)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "GetUserRoles";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("name", DbType.String).Value = name;

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd;

            DataTable dt = new DataTable();
            ada.Fill(dt);
            ada.Dispose();

            List<string> names = new List<string>();
            foreach (DataRow row in dt.Rows) names.Add(row["RoleName"] as string);

            return names.ToArray();
        }

        void IAccountStorage.AddFriend(string user, string friend)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"AddFriend";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("user", DbType.String).Value = user;
            cmd.Parameters.Add("friend", DbType.String).Value = friend;

            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        void IAccountStorage.DeleteFriend(Int64 userId, Int64 friendId)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"DeleteFriend";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("user", DbType.Int64).Value = userId;
            cmd.Parameters.Add("friend", DbType.Int64).Value = friendId;

            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        void IAccountStorage.DeleteUser(Int64 id)
        {

            SqlConnection conn = new SqlConnection(m_ConnectionString);
            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(@"DeleteUser");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("id", DbType.Int64).Value = id;
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        void IAccountStorage.DeleteGroup(Int64 id)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(@"DeleteGroup");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("id", DbType.Int64).Value = id;
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        void IAccountStorage.CreateUser(String name, String nickname, String password, String email)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            conn.Open();
            try
            {
                SqlCommand insertUser = new SqlCommand("CreateUser", conn);
                insertUser.CommandType = CommandType.StoredProcedure;

                insertUser.Parameters.Add("name", DbType.String).Value = name;
                insertUser.Parameters.Add("password", DbType.String).Value = Utility.MD5(password);
                insertUser.Parameters.Add("nickname", DbType.String).Value = nickname;
                insertUser.Parameters.Add("email", DbType.String).Value = email;
                insertUser.Parameters.Add("inviteCode", DbType.String).Value = Guid.NewGuid().ToString().ToUpper().Replace("-", "");

                insertUser.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        void IAccountStorage.CreateTempUser(String name, String nickname)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            conn.Open();
            try
            {
                SqlCommand insertUser = new SqlCommand("CreateTempUser", conn);
                insertUser.CommandType = CommandType.StoredProcedure;

                insertUser.Parameters.Add("name", DbType.String).Value = name;
                insertUser.Parameters.Add("nickname", DbType.String).Value = nickname;

                insertUser.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        void IAccountStorage.CreateGroup(String creator, String name, String nickname)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);
            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand("CreateGroup", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("creator", DbType.String).Value = creator;
                cmd.Parameters.Add("name", DbType.String).Value = name;
                cmd.Parameters.Add("nickname", DbType.String).Value = nickname;
                cmd.Parameters.Add("inviteCode", DbType.String).Value = Guid.NewGuid().ToString().ToUpper().Replace("-", "");

                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        void IAccountStorage.UpdateUserInfo(string name, Hashtable values)
        {
            SqlConnection conn = new SqlConnection(m_ConnectionString);

            conn.Open();
            try
            {
                if (values.ContainsKey("Password"))
                {
                    if (!values.ContainsKey("PreviousPassword")) throw new Exception("原密码错误！");

                    SqlCommand checkPwdCmd = new SqlCommand();
                    checkPwdCmd.Connection = conn;
                    checkPwdCmd.CommandText = "select [Key] from Users where UpperName = @Name and Password = @Password";

                    checkPwdCmd.Parameters.Add("Name", DbType.String).Value = name.ToUpper();
                    checkPwdCmd.Parameters.Add("Password", DbType.String).Value = Utility.MD5(values["PreviousPassword"].ToString());

                    object val = checkPwdCmd.ExecuteScalar();

                    if (val == null) throw new Exception("原密码错误！");
                }

                StringBuilder cmdText = new StringBuilder();
                cmdText.Append("update Users set Name = Name");
                if (values.ContainsKey("Nickname")) cmdText.Append(",Nickname = @Nickname");
                if (values.ContainsKey("Password")) cmdText.Append(",Password = @Password");
                if (values.ContainsKey("EMail")) cmdText.Append(",EMail = @EMail");
                if (values.ContainsKey("InviteCode")) cmdText.Append(",InviteCode = @InviteCode");
                if (values.ContainsKey("AcceptStrangerIM")) cmdText.Append(",AcceptStrangerIM = @AcceptStrangerIM");
                if (values.ContainsKey("MsgFileLimit")) cmdText.Append(",MsgFileLimit = @MsgFileLimit");
                if (values.ContainsKey("MsgImageLimit")) cmdText.Append(",MsgImageLimit = @MsgImageLimit");
                if (values.ContainsKey("HomePage")) cmdText.Append(",HomePage = @HomePage");
                if (values.ContainsKey("HeadIMG")) cmdText.Append(",HeadIMG = @HeadIMG");
                if (values.ContainsKey("Remark")) cmdText.Append(",Remark = @Remark");
                cmdText.Append(" where UpperName=@UpperName");
                if (values.ContainsKey("PreviousPassword")) cmdText.Append(" and Password = @PreviousPassword");
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = cmdText.ToString();

                if (values.ContainsKey("Nickname")) cmd.Parameters.Add("Nickname", DbType.String).Value = values["Nickname"];
                if (values.ContainsKey("Password")) cmd.Parameters.Add("Password", DbType.String).Value = Utility.MD5(values["Password"] as string);
                if (values.ContainsKey("EMail")) cmd.Parameters.Add("EMail", DbType.String).Value = values["EMail"];
                if (values.ContainsKey("InviteCode")) cmd.Parameters.Add("InviteCode", DbType.String).Value = values["InviteCode"];

                if (values.ContainsKey("AcceptStrangerIM")) cmd.Parameters.Add("AcceptStrangerIM", DbType.Int64).Value = ((bool)values["AcceptStrangerIM"]) ? 1 : 0;
                if (values.ContainsKey("MsgFileLimit")) cmd.Parameters.Add("MsgFileLimit", DbType.Int64).Value = Convert.ToInt64((Double)values["MsgFileLimit"]);
                if (values.ContainsKey("MsgImageLimit")) cmd.Parameters.Add("MsgImageLimit", DbType.Int64).Value = Convert.ToInt64((Double)values["MsgImageLimit"]);

                if (values.ContainsKey("HomePage")) cmd.Parameters.Add("HomePage", DbType.String).Value = values["HomePage"];
                if (values.ContainsKey("HeadIMG")) cmd.Parameters.Add("HeadIMG", DbType.String).Value = values["HeadIMG"];
                if (values.ContainsKey("Remark")) cmd.Parameters.Add("Remark", DbType.String).Value = values["Remark"];

                cmd.Parameters.Add("UpperName", DbType.String).Value = name.ToUpper();
                if (values.ContainsKey("PreviousPassword")) cmd.Parameters.Add("PreviousPassword", DbType.String).Value = Utility.MD5(values["PreviousPassword"] as string);

                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        String[] IAccountStorage.GetGroupManagers(string name)
        {
            DataTable result = new DataTable();

            SqlConnection conn = new SqlConnection(m_ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = @"GetGroupManagers";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("name", DbType.String).Value = name;

            SqlDataAdapter ada = new SqlDataAdapter();
            ada.SelectCommand = cmd;

            ada.Fill(result);
            ada.Dispose();

            List<String> names = new List<string>();
            foreach (DataRow row in result.Rows) names.Add(row["Name"] as string);

            return names.ToArray();
        }

    }
}
#pragma warning restore 618